import { Service } from 'egg';
import { IsClass } from '../model/IsClass';
interface tokenType {
  username: string;
  password: string;
  type: number;
}
export default class ClassService extends Service {
  /**
   * 查询所有班级数据
   * @param decode token
   */
  public async queryClassAll(decode:tokenType) {
    if (decode.type !== 3) {
      return {
        code: 1,
        message: '该用户无权限操作',
        data: [],
      };
    }
    try {
      const result = await this.app.mysql.select('classes') as IsClass[];
      return {
        code: 0,
        message: '操作成功',
        data: result,
      };
    } catch (error) {
      return {
        code: 1,
        message: error,
        data: [],
      };
    }
  }

  /**
   * 添加班级
   * @param classname 班级名称
   * @param decode 用户信息
   */
  public async addClass(classname:string, decode:tokenType) {
    if (decode.type !== 3) {
      return {
        code: 1,
        message: '该用户无权限操作',
        data: [],
      };
    }
    const result = await this.app.mysql.beginTransactionScope(async conn => {
      const classArr = await conn.select('classes', { where: { classname } }) as IsClass[];
      console.log(classArr);
      if (classArr.length !== 0) {
        return {
          code: 1,
          message: '该班级已存在',
          data: [],
        };
      }
      await conn.insert('classes', {
        classname,
        created_time: new Date(),
        updated_time: new Date(),
      });
      return {
        code: 0,
        message: '添加班级成功',
        data: [],
      };
    }, this.ctx);
    return result;
  }

  /**
   * 删除班级
   * @param id 班级id
   * @param decode 用户信息
   */
  public async delClass(id:number, decode:tokenType) {
    if (decode.type !== 3) {
      return {
        code: 1,
        message: '该用户无权限操作',
        data: [],
      };
    }
    interface CountType {
      teachers: number;
      students: number;
    }
    const sql = 'SELECT COUNT(teachers.class_id) as teachers, COUNT(students.class_id) as students FROM classes LEFT JOIN teachers ON teachers.class_id = classes.id LEFT JOIN students ON students.class_id = classes.id WHERE classes.id = ?';
    const result = await this.app.mysql.beginTransactionScope(async conn => {
      const classArr = await conn.select('classes', { id }) as IsClass[];
      if (classArr.length === 0) {
        return {
          code: 1,
          message: '该班级不存在',
          data: [],
        };
      }
      const counts = await conn.query(sql, [ id ]) as CountType[];
      if (counts[0].teachers !== 0 || counts[0].students !== 0) {
        return {
          code: 1,
          message: '该班级存在学生或老师,不可删除',
          data: [],
        };
      }
      await conn.delete('classes', { id });
      return {
        code: 0,
        message: '删除班级成功',
        data: [],
      };
    }, this.ctx);
    return result;
  }

  /**
   * 查询班级列表
   * @param decode 用户信息
   * @param params 班级查询条件
   */
  public async queryClassList(decode:tokenType, params) {
    if (decode.type !== 3) {
      return {
        code: 1,
        message: '该用户无权限操作',
        data: [],
      };
    }
    const result = await this.app.mysql.beginTransactionScope(async conn => {
      const sql = `SELECT classes.id,classes.classname, COUNT(teachers.class_id) as teacherNum, COUNT(students.class_id) as studentNum FROM classes 
                LEFT JOIN teachers ON teachers.class_id = classes.id 
                LEFT JOIN students ON students.class_id = classes.id
                 WHERE classes.classname LIKE '%${params.classname.trim()}%'
                GROUP BY classes.id LIMIT ${(params.current - 1) * params.pageSize}, ${params.pageSize}`;
      const classList = await conn.query(sql);
      const pageSizeSql = `SELECT COUNT(*) as total FROM classes WHERE classname LIKE '%${params.classname.trim()}%'`;
      const pageSize = await conn.query(pageSizeSql);
      return {
        code: 0,
        message: '操作成功',
        data: { classList, total: pageSize[0].total },
      };
    }, this.ctx);
    return result;
  }
}
